package R.Dao;


import R.Entity.Record;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class RegDao {
    //数据库连接
    public static Connection getConn() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/hospital", "root", "123456");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    //新增数据库
    public static int addR2(Record r) {
        int sta = 0;
        try {
            if (RegDao.PD(r)) {
                Connection conn = RegDao.getConn();
                String sql = "insert into registrationrecord(CardID,RegistrationID,Name,Gender,Age,DoctorName,Department,RegType,Venue,Fee,RegDate,Operator,DepName) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
                PreparedStatement ps = conn.prepareStatement(sql);//预编译对象
                //赋值
                ps.setString(1, r.getCardID());
                ps.setString(2, r.getRegistrationID());
                ps.setString(3, r.getName());
                ps.setString(4, r.getGender());
                ps.setInt(5, r.getAge());
                ps.setString(6, r.getDoctorName());
                ps.setString(7, r.getDepartment());
                ps.setString(8, r.getRegType());
                ps.setString(9, r.getVenue());
                ps.setDouble(10, r.getFee());
                ps.setString(11, r.getRegDate());
                ps.setString(12, r.getOperator());
                ps.setString(13, r.getDepName());
                sta = ps.executeUpdate();
                ps.close();
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sta;
    }

    //新增时判断CardID(就诊卡号)是否已存在
    public static boolean PD(Record r) {
        boolean a = true;
        try {
            Connection conn = RegDao.getConn();
            String sql = "select CardID from registration where CardID='" + r.getCardID() + "'";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                a = false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return a;
    }

    //显示所有数据
    public static List<Record> showAll() {
        List<Record> list = new ArrayList<Record>();
        try {
            Connection conn = RegDao.getConn();
            String sql = "select CardID,RegistrationID,Name,Gender,Age,DoctorName,Department,RegType,Venue,Fee,RegDate,Operator,DepName from registrationrecord where zt=1";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Record r = new Record();
                String CardID = rs.getString(1);//就诊卡号
                String RegistrationID = rs.getString(2);//挂号单编号
                String Name = rs.getString(3);//病人姓名
                String Gender = rs.getString(4);//病人性别
                int Age = rs.getInt(5);//病人年龄
                String DoctorName = rs.getString(6);//就诊医生
                String Department = rs.getString(7);//挂号类型
                String RegType = rs.getString(8);//门诊类型
                String Venue = rs.getString(9);//就诊地点
                double Fee = rs.getDouble(10);//挂号费用
                String RegDate = rs.getString(11);//就诊时间
                String Operator = rs.getString(12);//工作人员
                String DepName = rs.getString(13);//挂号科室
                r.setCardID(CardID);
                r.setRegistrationID(RegistrationID);
                r.setName(Name);
                r.setGender(Gender);
                r.setAge(Age);
                r.setDoctorName(DoctorName);
                r.setDepartment(Department);
                r.setRegType(RegType);
                r.setVenue(Venue);
                r.setFee(Fee);
                r.setRegDate(RegDate);
                r.setOperator(Operator);
                r.setDepName(DepName);
                list.add(r);
            }
            conn.close();
            ps.close();
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    //查询数据(根据就诊卡号和编号)
    public static List<Record> ChaXun(Record a) {
        List<Record> list = new ArrayList<Record>();
        try {
            Connection conn = RegDao.getConn();
            String sql = "select CardID,RegistrationID,Name,Gender,Age,DoctorName,Department,RegType,Venue,Fee,RegDate,Operator,DepName from registrationrecord where zt=1 and CardID='" + a.getCardID() + "' and RegistrationID='" + a.getRegistrationID() + "'";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Record r = new Record();
                String CardID = rs.getString(1);//就诊卡号
                String RegistrationID = rs.getString(2);//挂号单编号
                String Name = rs.getString(3);//病人姓名
                String Gender = rs.getString(4);//病人性别
                int Age = rs.getInt(5);//病人年龄
                String DoctorName = rs.getString(6);//就诊医生
                String Department = rs.getString(7);//挂号类型
                String RegType = rs.getString(8);//门诊类型
                String Venue = rs.getString(9);//就诊地点
                double Fee = rs.getDouble(10);//挂号费用
                String RegDate = rs.getString(11);//就诊时间
                String Operator = rs.getString(12);//工作人员
                String DepName = rs.getString(13);//挂号科室
                r.setCardID(CardID);
                r.setRegistrationID(RegistrationID);
                r.setName(Name);
                r.setGender(Gender);
                r.setAge(Age);
                r.setDoctorName(DoctorName);
                r.setDepartment(Department);
                r.setRegType(RegType);
                r.setVenue(Venue);
                r.setFee(Fee);
                r.setRegDate(RegDate);
                r.setOperator(Operator);
                r.setDepName(DepName);
                list.add(r);
            }
            conn.close();
            ps.close();
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    //修改时查询返回实体类
    public static Record getR(Record a) {
        Record r = new Record();
        try {
            Connection conn = RegDao.getConn();
            String sql = "select CardID,RegistrationID,Name,Gender,Age,DoctorName,Department,RegType,Venue,Fee,RegDate,Operator,DepName from registrationrecord where zt=1 and RegistrationID='" + a.getRegistrationID() + "'";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                String CardID = rs.getString(1);//就诊卡号
                String RegistrationID = rs.getString(2);//挂号单编号
                String Name = rs.getString(3);//病人姓名
                String Gender = rs.getString(4);//病人性别
                int Age = rs.getInt(5);//病人年龄
                String DoctorName = rs.getString(6);//就诊医生
                String Department = rs.getString(7);//挂号类型
                String RegType = rs.getString(8);//门诊类型
                String Venue = rs.getString(9);//就诊地点
                double Fee = rs.getDouble(10);//挂号费用
                String RegDate = rs.getString(11);//就诊时间
                String Operator = rs.getString(12);//工作人员
                String DepName = rs.getString(13);//挂号科室
                r.setCardID(CardID);
                r.setRegistrationID(RegistrationID);
                r.setName(Name);
                r.setGender(Gender);
                r.setAge(Age);
                r.setDoctorName(DoctorName);
                r.setDepartment(Department);
                r.setRegType(RegType);
                r.setVenue(Venue);
                r.setFee(Fee);
                r.setRegDate(RegDate);
                r.setOperator(Operator);
                r.setDepName(DepName);
            }
            rs.close();
            ps.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return r;
    }

    //修改数据
    public static int udateR(Record r) {
        int sta = 0;
        try {
            String CardID = r.getCardID();
            String RegistrationID = r.getRegistrationID();
            String Name = r.getName();
            int Age = r.getAge();
            String DoctorName = r.getDoctorName();
            String RegDate = r.getRegDate();
            String Operator = r.getOperator();
            String Gender = r.getGender();
            String Department = r.getDepartment();
            String RegType = r.getRegType();
            String Venue = r.getVenue();
            double Fee = r.getFee();
            String DepName = r.getDepName();
            if (RegDate==""){
                RegDate="now()";
            }
            Connection conn = RegDao.getConn();
            String sql = "update registrationrecord set CardID=?,RegistrationID=?,Name=?,Gender=?,Age=?,DoctorName=?,Department=?,RegType=?,Venue=?,Fee=?,RegDate=?,Operator=?,DepName=? where CardID='" + r.getCardID() + "'";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, CardID);
            ps.setString(2, RegistrationID);
            ps.setString(3, Name);
            ps.setString(4, Gender);
            ps.setInt(5, Age);
            ps.setString(6, DoctorName);
            ps.setString(7, Department);
            ps.setString(8, RegType);
            ps.setString(9, Venue);
            ps.setDouble(10, Fee);
            ps.setString(11, RegDate);
            ps.setString(12, Operator);
            ps.setString(13, DepName);
            sta = ps.executeUpdate();
            ps.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sta;
    }

    //删除（把状态改成0）
    public static int delR(Record r) {
        int sta = 0;
        try {
            String RegistrationID = r.getRegistrationID();
            Connection conn = RegDao.getConn();
            String sql = "update registrationrecord set zt=0 where RegistrationID='" + RegistrationID + "'";
            PreparedStatement ps = conn.prepareStatement(sql);
            sta = ps.executeUpdate();
            ps.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sta;
    }
}
